create database MySweetHome
go
use MySweetHome
go
create table swhUser
(
	IDUser nvarchar(100) primary key,
	Account nvarchar(100) not null,
	[password] nvarchar(100) not null,
	Email nvarchar(200),
	TypeUser nvarchar(30),
	Fullname nvarchar(200),
	birthday datetime, 
	sex nvarchar(20),
	age int,
	City nvarchar(100),
	[Address] nvarchar(1000),
	[Security] nvarchar(50),
	active nvarchar(10) default('true')
)

create table House
(
	IdHouse nvarchar(100) primary key,
	IDUser nvarchar(100) references swhUser(IDUser), 
	tittlehouse nvarchar(1000),
	DateUp Datetime,
	SquarefootageEarth nvarchar(10),
	SquarefootageHouse nvarchar(10),
	city nvarchar(100),
	location nvarchar(100),
	bedroom int,
	bathroom int,
	livingroom int,
	typehouse nvarchar(100),
	homestyle nvarchar(100),
	deposit float,
	introduction nvarchar(max),
	typepayment nvarchar(100),
	linkmap nvarchar(max)	
)
go
create table linkImage
(
	id int identity(1,1) primary key,
	idImage nvarchar(100) references House(IdHouse),
	linkImageHouse nvarchar(max)
)
go
create table FAQs
(
	IdQuestion int identity(1,1) primary key,	
	Question nvarchar(max),
	answers nvarchar(max)
)
go
create table administrator
(
	account nvarchar(100) primary key,
	[password] nvarchar(100)
)
go
create table savemessage
(
	idmessage int identity(1,1) primary key,
	IdHouse nvarchar(100) references House(	),
	IDUser nvarchar(100) references swhUser(IDUser)
)
go
create proc login
	@Account nvarchar(100),
	@password nvarchar(100)
as
	select * from swhUser where Account=@Account and [Password]=@password
go
insert into swhUser values(1,'ninhfanmu','a123456','ninhfanmu@gmail.com','buyer','nguyenhaininh','01/01/2012','nam',21,'bacninh','tiendu','fafasfa','true');
insert into swhUser values(2,'sa','sa','ninhfanmu@gmail.com','buyer','nguyenhaininh','01/01/2012','nam',21,'bacninh','tiendu','fafasfa','true');
insert into swhUser values(3,'canhdemo','adim','ninhfanmu@gmail.com','buyer','nguyenhaininh','01/01/2012','nam',21,'bacninh','tiendu','fafasfa','true');
insert into swhUser values('1234ff-f3vbg3-ff3f','quangdemo','123456','ninhfanmu@gmail.com','buyer','nguyenhaininh','01/01/2012','nam',21,'bacninh','tiendu','fafasfa','true');

go
select * from swhUser
go
create proc login
	@Account nvarchar(100),
	@password nvarchar(100)
as
	select * from swhUser where Account=@Account and [Password]=@password
go
create proc changepassword
	@Account nvarchar(100),
	@password nvarchar(100)
as
	update swhUser set [password]=@password where Account=@Account
go
create proc showAllUser
as	
	select * from swhUser
go
create proc deleteUser
	@IDUser nvarchar(100)
as
delete from swhUser where IDUser=@IDUser
go
create proc searchUserByAccount
	@Account nvarchar(100)
as
 select * from swhUser where Account like @Account+'%'
go
create proc searchUserByFullname
@Fullname nvarchar(200)
as 
	select * from swhUser where Fullname like @Fullname+'%'

go
insert into swhUser values ('fw34gt-veh2-bew5hf-r2fsg2t', 'quangdemo', '123123', 'tuminhquang91@gmail.com', 'Person', 'Tu minh quang', 11/18/1991, 'Male', 21, 'Ha Noi', 'Thuong Tin Village', 'ewzgerhreGr32qt4atgeqt43tg4qt43', 'true')
go
insert into swhUser values ('th6uk8-45f7-34f6h7-23ft7uk', 'admindemo', '123123', 'adin@gmail.com', 'company', 'Administrtor', 11/18/1991, 'Male', 21, 'Ha Noi', 'Thuong Tin Village', 'hrehrea6htry5qt536f54greqt43few', 'true')
go
select * from House where IdHouse='d1f1ca33-cfa6-4024-919b-dc8a53650b17'
GO
SELECT * from linkImage where linkImageHouse='525522ef-ee64-419d-8e4d-7594012e7b9e.jpg'
go
select * from savemessage

--delete from swhUser where Account = 'quangdemo' and IDUser = '1234ff-f3vbg3-ff3f'

 